package com.food.dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import com.food.dao.in.ProductDAO;
import com.food.model.Product;

public class ProductDAOJdbc implements ProductDAO {
	// private static final String URL =
	// "jdbc:sqlserver://192.168.34.108:1433;database=PJDB";
	// private static final String USER = "sa";
	// private static final String PASSWORD = "sa123456";

	private DataSource ds = null;

	public ProductDAOJdbc() {
		try {
			Context ctx = new InitialContext();
			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/food");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private static final String INSERT = "insert into product (pname, pprice, pcooker, pcountry, ppath, pintroduce) values(?,?,?,?,?,?)";

	// �s�W���~
	@Override
	public void insertProduct(Product bean) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(INSERT);

			stmt.setString(1, bean.getPname());
			stmt.setInt(2, bean.getPprice());
			stmt.setString(3, bean.getPcooker());
			stmt.setString(4, bean.getPcountry());
			stmt.setString(5, bean.getPpath());
			stmt.setString(6, bean.getPintroduce());
		

			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private static final String UPDATE = "update product set pname =?, pprice =?, pcooker =?, pcountry =?, ppath =?, pintroduce=? where pid=?";

	// ��s���~
	@Override
	public void updateProduct(String pname, int pprice, String pcooker,
			String pcountry, String ppath, String pintroduce, int pid) {
		Product result = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(UPDATE);

			stmt.setString(1, pname);
			stmt.setInt(2, pprice);
			stmt.setString(3, pcooker);
			stmt.setString(4, pcountry);
			stmt.setString(5, ppath);
			stmt.setString(6, pintroduce);
			stmt.setInt(7, pid);

			int i = stmt.executeUpdate();
			if (i == 1) {
				result = new Product();
				result.setPname(pname);
				result.setPprice(pprice);
				result.setPcooker(pcooker);
				result.setPpath(ppath);
				result.setPintroduce(pintroduce);
				result.setPid(pid);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private static final String DELETE = "delete from product where pid =?";

	// �R�����~
	@Override
	public void deleteProduct(int pid) {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(DELETE);

			stmt.setInt(1, pid);
			stmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private static final String SELECT1 = "select pid, pname, pprice, pcooker, pcountry, ppath, pintroduce from product";

	// �d�ߩҦ����~
	@Override
	public List<Product> findAllProduct() {
		List<Product> result = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT1);
			rset = stmt.executeQuery();
			result = new ArrayList<Product>();
			while (rset.next()) {
				Product temp = new Product();

				temp.setPid(rset.getInt("pid"));
				temp.setPname(rset.getString("pname"));
				temp.setPprice(rset.getInt("pprice"));
				temp.setPcooker(rset.getString("pcooker"));
				temp.setPcountry(rset.getString("pcountry"));
				temp.setPpath(rset.getString("ppath"));
				temp.setPintroduce(rset.getString("pintroduce"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;

	}

	// private static final String SELECT_PAGE =
	// "select top ? pid, pname, pprice, pcooker, pcountry, ppath, pintroduce from product where pid not in ( select top ( ? * ? ) pid from product order by pid desc ) order by pid desc";

	public List<Product> findAllProductPages(int pagesize, int pageindex) {
		List<Product> result = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		String sql1 = "select top " + pagesize
				+ " pid, pname, pprice, pcooker, pcountry, ppath, pintroduce ";
		String sql2 = "from product where pid not in ( select top (" + pagesize
				+ "*" + pageindex + ") pid from product order by pid desc ) ";
		String sql3 = "order by pid desc";

		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(sql1 + sql2 + sql3);
			// stmt =
			// conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

			rset = stmt.executeQuery();
			result = new ArrayList<Product>();
			while (rset.next()) {
				Product temp = new Product();
				temp.setPid(rset.getInt("pid"));
				temp.setPname(rset.getString("pname"));
				temp.setPprice(rset.getInt("pprice"));
				temp.setPcooker(rset.getString("pcooker"));
				temp.setPcountry(rset.getString("pcountry"));
				temp.setPpath(rset.getString("ppath"));
				temp.setPintroduce(rset.getString("pintroduce"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;

	}

	//有條件查詢，顯示page
	
	@Override
	public List<Product> find_Search_Pages(int pagesize, int pageindex,String search) {
		List<Product> result = null;
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		String sql1 = "select top " +pagesize+ " pid, pname, pprice, pcooker, pcountry, ppath, pintroduce ";
		String sql2 = "from product where pname like '%"+search+"%' or pcooker like '%"+search+"%'";
		String sql3 = "	or  pcountry like '%"+search+"%' and pid not in ( select top (" + pagesize
				+ "*" + pageindex + ") pid from product where pname like '%"+search+"%' or pcooker like '%"+search+"%' or pcountry like '%"+search+"%' order by pid desc ) ";
		String sql4 = "order by pid desc";

		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(sql1 + sql2 + sql3 + sql4);
			// stmt =
			// conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

			rset = stmt.executeQuery();
			result = new ArrayList<Product>();
			while (rset.next()) {
				Product temp = new Product();
				temp.setPid(rset.getInt("pid"));
				temp.setPname(rset.getString("pname"));
				temp.setPprice(rset.getInt("pprice"));
				temp.setPcooker(rset.getString("pcooker"));
				temp.setPcountry(rset.getString("pcountry"));
				temp.setPpath(rset.getString("ppath"));
				temp.setPintroduce(rset.getString("pintroduce"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;

	}
	
	private static final String SELECT_NAME = "select pid, pname, pprice, pcooker, pcountry, ppath, pintroduce from product where pid like ?";
	
	@Override
	public List<Product> findProductByName(String pname){
			
		List<Product> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT_NAME);
			stmt.setString(1, "%" + pname + "%");
			rset = stmt.executeQuery();
			result = new ArrayList<Product>();
			while (rset.next()) {
				Product temp = new Product();

				temp.setPid(rset.getInt("pid"));
				temp.setPname(rset.getString("pname"));
				temp.setPprice(rset.getInt("pprice"));
				temp.setPcooker(rset.getString("pcooker"));
				temp.setPcountry(rset.getString("pcountry"));
				temp.setPpath(rset.getString("ppath"));
				temp.setPintroduce(rset.getString("pintroduce"));

				result.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return  result;
		
	}
	

	private static final String SELECT2 = "select pid, pname, pprice, pcooker, pcountry, ppath, pintroduce from product where pid=?";

	// �d��ID
	@Override
	public Product findOneProduct(int pid) {
		Product result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT2);
			stmt.setInt(1, pid);
			rset = stmt.executeQuery();

			while (rset.next()) {
				result = new Product();

				result.setPid(rset.getInt("pid"));
				result.setPname(rset.getString("pname"));
				result.setPprice(rset.getInt("pprice"));
				result.setPcooker(rset.getString("pcooker"));
				result.setPcountry(rset.getString("pcountry"));
				result.setPpath(rset.getString("ppath"));
				result.setPintroduce(rset.getString("pintroduce"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String SELECT3 = "select pid, pname, pprice, pcooker, pcountry, ppath, pintroduce from product where pcountry =?";

	// �d�ߥΰ��y�h�d��
	@Override
	public List<Product> findProductByCountry(String pcountry) {
		List<Product> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;
		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT3);
			stmt.setString(1, pcountry);
			rset = stmt.executeQuery();
			result = new ArrayList<Product>();

			while (rset.next()) {
				Product temp = new Product();

				temp.setPid(rset.getInt("pid"));
				temp.setPname(rset.getString("pname"));
				temp.setPprice(rset.getInt("pprice"));
				temp.setPcooker(rset.getString("pcooker"));
				temp.setPcountry(rset.getString("pcountry"));
				temp.setPpath(rset.getString("ppath"));
				temp.setPintroduce(rset.getString("pintroduce"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	private static final String SELECT4 = "select pid, pname, pprice, pcooker, pcountry, ppath from product where pcooker =?";

	// �d�߼p�v
	@Override
	public List<Product> findProductByCooker(String pcooker) {
		List<Product> result = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rset = null;

		try {
			// conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = this.ds.getConnection();
			stmt = conn.prepareStatement(SELECT4);
			stmt.setString(1, pcooker);
			rset = stmt.executeQuery();
			result = new ArrayList<Product>();

			while (rset.next()) {
				Product temp = new Product();

				temp.setPid(rset.getInt("pid"));
				temp.setPname(rset.getString("pname"));
				temp.setPprice(rset.getInt("pprice"));
				temp.setPcooker(rset.getString("pcooker"));
				temp.setPcountry(rset.getString("pcountry"));
				temp.setPpath(rset.getString("ppath"));

				result.add(temp);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}

	public static void main(String[] args) {
		//ProductDAO dao = new ProductDAOJdbc();

		int pagesize = 5;
		int pageindex = 0;
		String search = "台灣";
		
		String sql1 = "select top " + pagesize
				+ " pid, pname, pprice, pcooker, pcountry, ppath, pintroduce ";
		String sql2 = " where pname=" + search + " or " + "pcooker=" + search + " or " + " pcountry= " + search ;
		String sql3 = "from product where pid not in ( select top (" + pagesize
				+ "*" + pageindex + ") pid from product order by pid desc ) ";
		String sql4 = "order by pid desc";
		
		
		System.out.println(sql1+sql2+sql3+sql4);
		
		
		
		// �d�߼p�v
		// List<Product> bean = dao.findProductByCooker("阿嘎師");
		// System.out.println(bean);

		// �d�ߥΰ��y�h�d��
		// List<Product> bean1 = dao.findProductByCountry("日本");
		// System.out.println(bean1);

		// �d��ID
		// Product bean2 = dao.findOneProduct(10);
		// System.out.println(bean2);

		// �d�ߩҦ�
		// List<Product> bean3 = dao.findAllProduct();
		// System.out.println(bean3);

		// �R�����~
		// dao.deleteProduct(10);

		// �ק�
		// dao.updateProduct("美味料理", 40, "阿嘎師", "法國",
		// "http://www.youtube.com/embed/o0wgrjYg9Go", "天然無添加", 11);

		// �s�W
		// Product bean = new Product();
		// bean.setPcooker("8");
		// bean.setPintroduce("�p�n");
		// dao.insertProduct(bean);
		// System.out.println(bean);

	}

	
}
